package controller;

import controller.packing.*;
import javafx.collections.*;
import javafx.event.ActionEvent;
import javafx.event.Event;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.WindowEvent;

import java.net.URL;
import java.sql.*;
import java.time.LocalDate;
import java.util.*;

/**
 * 医生界面的controller
 */
public class Doctor implements Initializable
{
    /**
     * 数据库URL
     */
    static final String DB_URL = "jdbc:mysql://localhost:3306/hospital_data?useSSL=false&serverTimezone=UTC";
    /**
     * 数据库用户名
     */
    static final String USER = "root";
    /**
     * 数据库密码
     */
    static final String PASS = "1999";
    /**
     * 数据库连接对象
     */
    static Connection conn = null;
    /**
     * SQL语句对象
     */
    static Statement stmt = null,stmt2=null;
    /**
     * 病人列表
     */
    ObservableList<PatientList> pat_list = FXCollections.observableArrayList();
    /**
     * 收入列表
     */
    ObservableList<IncomeList> income_list = FXCollections.observableArrayList();

    @FXML
    TableView<PatientList> table_reg;
    @FXML
    TableView<IncomeList> table_income;
    /**
     * 退出登录和退出按钮
     */
    @FXML
    private Button btn_logout,btn_exit;
    /**
     * 列属性：挂号编号，病人名字，挂号时间，号别种，科室名称
     * 			医生编号，医生名称，好重类别，挂号人数，收入
     */
    @FXML
    private TableColumn<?, ?> col_regnum,col_patname,col_regtime,col_regtype
            ,col_officename,col_docnum,col_docname,col_regtype2,col_regcount,col_income,col_valid;
    /**
     * 起始日期和截至日期的文本框
     */
    @FXML
    TextField text_begin,text_end;
    /**
     * 起始日期和截至日期对象
     */
    @FXML
    DatePicker date_end,date_begin;

    /**
     * 初始化：绑定列属性
     * @param location
     * @param resources
     */
    @Override
    public void initialize(URL location, ResourceBundle resources)
    {
        //使用对象工厂绑定列属性
        date_begin.setValue(LocalDate.now());
        date_end.setValue(LocalDate.now());;
        col_regnum.setCellValueFactory(new PropertyValueFactory<>("regNum"));
        col_patname.setCellValueFactory(new PropertyValueFactory<>("patName"));
        col_regtime.setCellValueFactory(new PropertyValueFactory<>("regTime"));
        col_regtype.setCellValueFactory(new PropertyValueFactory<>("regType"));
        col_valid.setCellValueFactory(new PropertyValueFactory<>("valid"));
        col_officename.setCellValueFactory(new PropertyValueFactory<>("officeName"));
        col_docnum.setCellValueFactory(new PropertyValueFactory<>("docNum"));
        col_docname.setCellValueFactory(new PropertyValueFactory<>("docName"));
        col_regtype2.setCellValueFactory(new PropertyValueFactory<>("regType"));
        col_regcount.setCellValueFactory(new PropertyValueFactory<>("regCount"));
        col_income.setCellValueFactory(new PropertyValueFactory<>("totalIncome"));
    }

    /**
     * 更新表的显示内容
     * @param event
     */
    @FXML
    public void showRegisterInfo(Event event)
    {
        try
        {
            //加载日期
            String time_begin,time_end;
            LocalDate datetmp = date_begin.getValue();
            LocalDate datetmp2 = date_end.getValue();
            if(datetmp==null||datetmp2==null)
            {
                time_begin = LocalDate.now().toString();
                time_end = LocalDate.now().toString();
            }
            else
            {
                time_begin = datetmp.toString();
                time_end = datetmp2.toString();
            }
            time_begin += " 00:00:00";
            time_end += " 23:59:59";
            pat_list.clear();
            income_list.clear();
            //连接数据库对象
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
            stmt2 = conn.createStatement();
            String doc_num=Main.accountNum;
            //获取挂号信息
            String sql = "select register_num,registration_num,pat_num,"
                    + "reg_datetime,unreg from register where "
                    + "doc_num= '"+doc_num+"'";
            ResultSet rs = stmt.executeQuery(sql);
            String register_num=null,registration_num=null,pat_num=null;
            String reg_datetime=null,pat_name=null,expertstr=null;
            String office_name = null,doc_name=null,unreg_str=null;
            boolean expert=true,unreg=false;
            ResultSet rs2 =null;
            int regcount = 0;
            double totalcost = 0;;
            while(rs.next())
            {
                register_num = rs.getString("register_num");
                registration_num = rs.getString("registration_num");
                pat_num = rs.getString("pat_num");
                reg_datetime = rs.getString("reg_datetime");
                unreg = rs.getBoolean("unreg");
                unreg_str = unreg?"否":"是";

                sql = "select expert from registration where "
                        + "reg_num= '"+registration_num+"'";
                rs2 = stmt2.executeQuery(sql);
                if(rs2.next())
                {
                    expert = rs2.getBoolean("expert");
                }

                sql = "select pat_name from patient where"
                        + " pat_num= '"+pat_num+"'";
                rs2 = stmt2.executeQuery(sql);
                if(rs2.next())
                {
                    pat_name = rs2.getString("pat_name");
                }
                expertstr = expert? "专家号":"普通号";
                pat_list.add(new PatientList(register_num,pat_name,
                        reg_datetime,expertstr,unreg_str));
            }
            //查询医生和科室信息
            sql = "select office.office_name,register.doc_num,"
                    + "doctor.doc_name,registration.expert,"
                    + "count(register.doc_num),sum(register.reg_cost) "
                    + "from register,registration,doctor,office "
                    + "where "
                    + "register.registration_num=registration.reg_num "
                    + "and doctor.doc_num=register.doc_num "
                    + "and office.office_num=registration.office_num "
                    + "and register.reg_datetime>='"+time_begin+"' and "
                    + "register.reg_datetime<='"+time_end+"'"
                    + "group by register.doc_num,registration.expert";
            rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                office_name = rs.getString("office.office_name");
                doc_num = rs.getString("register.doc_num");
                doc_name = rs.getString("doctor.doc_name");

                expert = rs.getBoolean("registration.expert");
                regcount = rs.getInt("count(register.doc_num)");
                totalcost = rs.getDouble("sum(register.reg_cost)");
                expertstr = expert? "专家号":"普通号";
                income_list.add(new IncomeList(office_name, doc_num,doc_name,
                        expertstr,Integer.toString(regcount),Double.toString(totalcost)));
            }
            if(rs!=null)
                rs.close();
            if(rs2!=null)
                rs2.close();
            //关闭数据库
            stmt2.close();
            stmt.close();
            conn.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        finally
        {
            table_reg.setItems(pat_list);
            table_income.setItems(income_list);
        }
    }

    /**
     * 退出系统
     * @param event
     */
    @FXML
    public void exitBtnClicked(ActionEvent event)
    {
        Event.fireEvent(Main.getPrimaryStage(),
                new WindowEvent(Main.getPrimaryStage(), WindowEvent.WINDOW_CLOSE_REQUEST ));
    }

    /**
     * 退出登录
     * @param event
     */
    @FXML
    public void logoutBtnClicked(ActionEvent event)
    {
        Main.setLogin();
    }
}
